package user_client;

import database_objects.Author;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Connection;
import java.util.ArrayList;
import java.util.List;
import libraryproject.CreatorReturnedInformationPane;

/*
 * import library.user_creator.CreatorReturnedInformationPane;
 */
/**
 *
 * @author Pawel Rychly
 *
 */
public class LibrarianUserClient extends DataBaseClient {

    public LibrarianUserClient(Connection connection, String userName,
            String userPassword) {

        super(connection, userName, userPassword);
        // TODO Auto-generated constructor stub
    }

    private boolean checkUser(String name, String surname, String address)
            throws SQLException {
        Statement stmt = this.getConnection().createStatement();

        ResultSet res = stmt.executeQuery("select count(*) as num from "
                + "czytelnicy where ( replace(lower(imie),' ','')"
                + " = replace(lower('" + name + "'),' ', ''))"
                + " and ( replace(lower(nazwisko),' ','') "
                + " = replace(lower('" + surname + "'),' ', ''))"
                + " and ( replace(lower(adres),' ','') "
                + " = replace(lower('" + address + "'),' ', ''))");

        res.next();
        Integer num = res.getInt("num");
        System.out.println("LibrarianUserClient: checking");
        if (num > 0) {
            return true;
        }

        return false;
    }

    /**
     * Add new user to database;
     *
     * @throws SQLException
     */
    public Integer addClient(CreatorReturnedInformationPane pane,
            String name, String surname, String address)
            throws SQLException {
        // check does the client exists.
        // this.getConnection().setAutoCommit(false);
        try {
            if (!checkUser(name, surname, address)) {
                System.out.println("l-1");
                Statement stmt = this.getConnection().createStatement();
                // get the user id
                System.out.println("l-3");

                //ResultSet res = stmt.executeQuery(ALTER SEQUENCE )
                ResultSet res = stmt.executeQuery("select id_czytelnika_seq.NEXTVAL as ID from dual");
                res.next();
                Integer user_id = res.getInt("ID");
                System.out.println("l-4");
                // add user to table
                res = stmt.executeQuery("insert into czytelnicy	values("
                        + user_id + ",'" + name + "', '" + surname
                        + "','" + address + "')");

                // create user
                stmt.executeQuery("CREATE USER " + surname + "_"
                        + user_id.toString() + " IDENTIFIED BY "
                        + "pass" + user_id.intValue()
                        + " DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp "
                        + "QUOTA unlimited ON users  QUOTA unlimited ON "
                        + "system PROFILE "
                        + "default ");

                stmt.executeQuery("grant create session, select any table,"
                        + " insert any table to "
                        + surname + "_" + user_id.toString());

                stmt.executeQuery("grant UserClient to " + surname + "_"
                        + user_id.toString());

                stmt.close();
                res.close();
                this.getConnection().commit();
                pane.getLoginField().setText(
                        surname + "_" + user_id.toString());
                pane.getTemporaryPassword().setText(
                        "pass" + user_id.intValue());
                pane.setVisible(true);
                System.out.println("utworzono użytkownika");
                return user_id;
            }
        } catch (SQLException e) {
            System.out.println("SQLError");
            e.printStackTrace();
            this.getConnection().rollback();
        }



        // add user privileges
		/*
         * stmt.executeQuery("grant " + user_id + "IDENTIFIED BY" + password +
         * "DEFAULT TABLESPACE users TEMPORARY TABLESPACE temp " + "QUOTA
         * unlimited ON users + QUOTA unlimited ON system PROFILE " + "default
         * ");
         */
        return -1;
    }
    

    public void removeClient() {
    }

    /**
     * Accept borrowing book from library.
     */
    public void approveBorrowingBook() {
    }

    /**
     * Sends to database information about returning book.
     */
    public void noteReturningOfBook() {
    }

    /**
     * Adds book to database.
     */
    public void addBook() {
    }

    /**
     * Removes book from database.
     */
    public void removeBook() {
    }

    /**
     * Adds new section
     */
    public void addNewSection() {
    }

    /**
     * Removes section
     */
    public void removeSection() {
    }

    public Integer addBook(
            String isbnString,
            String positionOfBook) throws SQLException {

        ArrayList<String> cols = new ArrayList<String>();
        cols.add("pozycja_na_polce");
        cols.add("isbn");
        ArrayList<String> values = new ArrayList<String>();
        values.add(positionOfBook);
        values.add(isbnString);

        if (isExists(cols, values, "ksiazki")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            ResultSet res = stmt.executeQuery("select id_ksiazki_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");

            int val = stmt.executeUpdate("INSERT INTO ksiazki VALUES ("
                    + "" + result + ", " + positionOfBook
                    + ", " + isbnString + " )");
            stmt.close();

            stmt = this.getConnection().createStatement();

            /*
             * removeElement(isbnString, "autorstwa", "isbn"); for (Author
             * author : authors) { cols = new ArrayList<String>();
             * cols.add("id_autora"); cols.add("isbn");
             * values.add(author.getId()); values.add(isbnString);
             *
             * try { int val2 = stmt.executeUpdate("INSERT INTO autorstwa VALUES
             * (" + "" + author.getId().toString() + ", " + isbnString + " )");
             * } catch (SQLException e) { e.printStackTrace(); }
            }
             */

            this.getConnection().commit();
            return result;
            /*
             * ResultSet res = stmt.executeQuery("select id_dzialu_seq.NEXTVAL
             * as val from dual"); res.next(); Integer result =
             * res.getInt("val");
             */
            /*
             * int val = stmt.executeUpdate("INSERT INTO wydania VALUES (" + ""
             * +isbn+ ", '" + nameOfEdition + "', to_date('"+ dateOfEdition +
             * "', 'yyyy/mm/dd'), '" + title + "', " + idOfDepartment +" )");
             * this.getConnection().commit(); stmt.close();
             * System.out.println("INDEX:" + val); }
             *
             * return 1;
             */

        }
    }

    public void removeAuthorConnections() {
    }

    public Integer addEdition(String isbn, String nameOfEdition,
            String dateOfEdition, String title, String idOfDepartment,
            ArrayList<Author> authors)
            throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("isbn");

        ArrayList<String> values = new ArrayList<String>();
        values.add(isbn);

        if (isExists(cols, values, "wydania")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            /*
             * ResultSet res = stmt.executeQuery("select id_dzialu_seq.NEXTVAL
             * as val from dual"); res.next(); Integer result =
             * res.getInt("val");
             */
            int val = stmt.executeUpdate("INSERT INTO wydania VALUES ("
                    + "" + isbn + ", '" + nameOfEdition
                    + "', to_date('" + dateOfEdition + "', 'yyyy/mm/dd'), '"
                    + title + "', " + idOfDepartment + " )");

            for (Author author : authors) {
                try {
                    int val2 = stmt.executeUpdate("INSERT INTO autorstwa VALUES ("
                            + author.getId() + ", " + isbn + ")");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }


            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
        }

        return 1;
    }

    /**
     * Ads new department
     */
    public Integer addDepartment(String name, String num) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("nazwa");
        cols.add("nr_regalu");
        ArrayList<String> values = new ArrayList<String>();
        values.add(name);
        values.add(num);

        if (isExists(cols, values, "dzialy")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            ResultSet res = stmt.executeQuery(
                    "select id_dzialu_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");
            int val = stmt.executeUpdate("INSERT INTO dzialy VALUES ("
                    + result + " , '" + name + "', " + num + " )");
            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
            return result;
        }


    }

    public Integer addAuthor(
            String name, String surname) throws SQLException {
        ArrayList<String> cols = new ArrayList<String>();
        cols.add("imie");
        cols.add("nazwisko");
        ArrayList<String> values = new ArrayList<String>();
        values.add(name);
        values.add(surname);

        if (isExists(cols, values, "autorzy")) {
            return -1;
        } else {
            Statement stmt = this.getConnection().createStatement();
            ResultSet res = stmt.executeQuery(
                    "select id_autora_seq.NEXTVAL as val from dual");
            res.next();
            Integer result = res.getInt("val");
            int val = stmt.executeUpdate("INSERT INTO autorzy VALUES ( "
                    + result + ", '" + surname + "', '" + name + "' )");
            this.getConnection().commit();
            stmt.close();
            System.out.println("INDEX:" + val);
            return result;
        }


    }
}
